﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;

/// <summary>
/// Summary description for ClaimDAL
/// </summary>
public class ClaimDAL
{

   
    
	public ClaimDAL()
	{
		//
		// TODO: Add constructor logic here
		//
	}

   

    public int insertClaim( String accountNo,
                            String claimDescription,
                            String claimStatus,
                            decimal claimAmount,
                            DateTime claimDate,
                            int insuredID,
                            String staffID,
                            String docCategory1,
                            String docPath1,
                            String docCategory2,
                            String docPath2,
                            String docCategory3,
                            String docPath3,
                            String docCategory4,
                            String docPath4,
                            String docCategory5,
                            String docPath5,
                            String docCategory6,
                            String docPath6
                            )
    {
       

            string sql = "INSERT INTO CLAIM ("+
                            "accountNo," + 
                            "caseID," + 
                            "claimDescription," + 
                            "claimStatus," + 
                            "claimAmount," +
                            "claimDate," +
                           
                            "staffID," + 
                            "insuredID," + 
                            "docCategory1," + 
                            "docPath1," + 
                            "docCategory2," + 
                            "docPath2," + 
                            "docCategory3," + 
                            "docPath3," + 
                            "docCategory4," + 
                            "docPath4," + 
                            "docCategory5," + 
                            "docPath5," + 
                            "docCategory6," + 
                            "docPath6)" +
                            " values ("+
                            "@accountNo," + 
                            "@caseID," + 
                            "@claimDescription," + 
                            "@claimStatus," + 
                            "@claimAmount," +
                            "@claimDate," +
                           
                            "@staffID," + 
                            "@insuredID," + 
                            "@docCategory1," + 
                            "@docPath1," + 
                            "@docCategory2," + 
                            "@docPath2," + 
                            "@docCategory3," + 
                            "@docPath3," + 
                            "@docCategory4," + 
                            "@docPath4," + 
                            "@docCategory5," + 
                            "@docPath5," + 
                            "@docCategory6," + 
                            "@docPath6)";



            SqlConnection con = DBManager.getSqlConnection();
            con.Open();
            //AccountsManager manager = new AccountsManager();
           // int caseID2 = manager.getMaxValue("CLAIM", "caseID");

            int caseID = new AccountsManager().getMaxValue("CLAIM","caseID");
           

            SqlCommand cmd = new SqlCommand(sql, con);
            cmd.Parameters.AddWithValue("@accountNo", accountNo);
            cmd.Parameters.AddWithValue("@caseID", caseID+1);
            cmd.Parameters.AddWithValue("@claimDescription", claimDescription);
            cmd.Parameters.AddWithValue("@claimStatus", claimStatus);
            cmd.Parameters.AddWithValue("@claimDate", DateTime.Now);

            cmd.Parameters.AddWithValue("@staffID", staffID);
            cmd.Parameters.AddWithValue("@claimAmount", claimAmount);
            cmd.Parameters.AddWithValue("@insuredID", insuredID);
            cmd.Parameters.AddWithValue("@docCategory1", docCategory1);
            cmd.Parameters.AddWithValue("@docPath1", docPath1);
            cmd.Parameters.AddWithValue("@docCategory2", docCategory2);
            cmd.Parameters.AddWithValue("@docPath2", docPath2);
            cmd.Parameters.AddWithValue("@docCategory3", docCategory3);
            cmd.Parameters.AddWithValue("@docPath3", docPath3);
            cmd.Parameters.AddWithValue("@docCategory4", docCategory4);
            cmd.Parameters.AddWithValue("@docPath4", docPath4);
            cmd.Parameters.AddWithValue("@docCategory5", docCategory5);
            cmd.Parameters.AddWithValue("@docPath5", docPath5);
            cmd.Parameters.AddWithValue("@docCategory6", docCategory6);
            cmd.Parameters.AddWithValue("@docPath6", docPath6);
            cmd.ExecuteNonQuery();
            
            con.Close();
            return caseID+1;

    }

    public DataSet getPendingClaimList()
    {
        
        SqlConnection con = DBManager.getSqlConnection();

        String sql = "SELECT INSURED.insuredID, CASE INSURANCE.productType WHEN 'H' THEN 'Hospitalisation' WHEN 'A' THEN 'Accident' END AS productType, INSURANCE.category, CLAIM.caseID, CLAIM.claimStatus, CONVERT(NVARCHAR(20),CLAIM.claimDate,103) AS claimDate"+
                     " FROM CLAIM INNER JOIN" +
                     " INSURED ON CLAIM.insuredID = INSURED.insuredID INNER JOIN" +
                     " INSURANCE ON INSURANCE.accountNo = INSURED.accountNo" +
                     " WHERE CLAIM.claimStatus =@claimStatus ";
        SqlCommand cmd = new SqlCommand(sql, con);
        cmd.Parameters.AddWithValue("@claimStatus", "PENDING");


        SqlDataAdapter myAdapter = new SqlDataAdapter(cmd);

        DataSet myDs = new DataSet();

        con.Open();
        myAdapter.Fill(myDs);

        con.Close();
        return myDs;


    }

    public DataSet getClaimApplyInfo(int insuredID)
    {

        SqlConnection con = DBManager.getSqlConnection();

        String sql = " SELECT D.planType, I.accountNo, C.caseID, C.claimDescription, C.claimStatus, C.claimAmount, C.claimDate, C.handledDate, C.insuredID, C.staffID, C.docCategory1, C.docPath1, C.docCategory2, C.docPath2, C.docCategory3, C.docPath3, C.docCategory4, C.docPath4, C.docCategory5, C.docPath5, C.docCategory6, C.docPath6" +
                     " FROM CLAIM C, Insured D, Insurance I " +
                     " WHERE " +
                     " C.insuredID=D.insuredID and " +
                     " D.accountNo=I.accountNo and " +
                     " C.insuredID=@insuredID";

        SqlCommand cmd = new SqlCommand(sql, con);
        cmd.Parameters.AddWithValue("@insuredID", insuredID);

        SqlDataAdapter myAdapter = new SqlDataAdapter(cmd);
        DataSet myDs = new DataSet();

        con.Open();
        myAdapter.Fill(myDs,"docsTab");

        con.Close();
        return myDs;

    }

    public void transferClaimAmount(String savingAccountNo, Decimal amount)
    {
        SqlConnection con = DBManager.getSqlConnection();
        con.Open();
        String sql = "insert into Summary (transactionDate,transactionType,transactionStatus,transactionAmount,accountNo) values (@transactionDate,@transactionType,@transactionStatus,@transactionAmount,@accountNo)";
        SqlCommand cmd = new SqlCommand(sql, con);

       
        cmd.Parameters.AddWithValue("@transactionDate", DateTime.Now);
        cmd.Parameters.AddWithValue("@transactionType", "INSURANCE CLAIM");
        cmd.Parameters.AddWithValue("@transactionStatus", "SUCCESS");
        cmd.Parameters.AddWithValue("@transactionAmount", amount);
        cmd.Parameters.AddWithValue("@accountNo", savingAccountNo);

        cmd.ExecuteNonQuery();


        sql = " update SAVING_ACCOUNT " +
            " set balance =balance+@amount" +
            " where accountNo=@accontNo";
        cmd = new SqlCommand(sql, con);
        cmd.Parameters.AddWithValue("@amount", amount);
        cmd.Parameters.AddWithValue("@accontNo", savingAccountNo);
        cmd.ExecuteNonQuery();

        con.Close();

    }

    public void confirm(int caseId, decimal amount,String staffID,String claimDescription)
    {
        SqlConnection con = DBManager.getSqlConnection();
        con.Open();
        String sql = "UPDATE CLAIM SET " +
                      "claimAmount=@amount, " +
                      "handledDate=@handledDate," +
                      "staffID=@staffID," +
                      "claimDescription=@claimDescription, " +
                      "claimStatus=@claimStatus" +
                      " where caseId=@caseId";
                      
        SqlCommand cmd = new SqlCommand(sql, con);
        cmd.Parameters.AddWithValue("@amount", amount);
        cmd.Parameters.AddWithValue("@staffID", staffID);
        cmd.Parameters.AddWithValue("@handledDate", DateTime.Now);
        cmd.Parameters.AddWithValue("@claimDescription", claimDescription);
        cmd.Parameters.AddWithValue("@claimStatus", "APPROVED");
        cmd.Parameters.AddWithValue("@caseId", caseId);
        cmd.ExecuteNonQuery();
        con.Close();

    }

    public void denied(int caseId, decimal amount, String staffID, String claimDescription)
    {
        SqlConnection con = DBManager.getSqlConnection();
        con.Open();
        String sql = "UPDATE CLAIM SET " +
                      "claimAmount=@amount, " +
                      "handledDate=@handledDate," +
                      "staffID=@staffID," +
                      "claimDescription=@claimDescription, " +
                      "claimStatus=@claimStatus" +
                      " where caseId=@caseId";

        SqlCommand cmd = new SqlCommand(sql, con);
        cmd.Parameters.AddWithValue("@amount", 0);
        cmd.Parameters.AddWithValue("@staffID", staffID);
        cmd.Parameters.AddWithValue("@handledDate", DateTime.Now);
        cmd.Parameters.AddWithValue("@claimDescription", claimDescription);
        cmd.Parameters.AddWithValue("@claimStatus", "DENIED");
        cmd.Parameters.AddWithValue("@caseId", caseId);
        cmd.ExecuteNonQuery();
        con.Close();

    }


    public decimal getTotalClaimAmount(int insuredID)
    {
        SqlConnection con = DBManager.getSqlConnection();
        con.Open();
        String sql = "Select startDate,endDate from Insured  where  insuredID=@insuredID";
        SqlCommand cmd = new SqlCommand(sql, con);
        cmd.Parameters.AddWithValue("@insuredID", insuredID);
        SqlDataReader dr = cmd.ExecuteReader();

        DateTime startDate = DateTime.MinValue;
        DateTime endDate = DateTime.MinValue;
        if (dr.Read())
        {
           startDate = dr.GetDateTime(0);
           endDate = dr.GetDateTime(1);  
        }
        dr.Close();
        startDate = startDate.AddYears(1);
        sql = "select sum(claimAmount) from CLAIM C,Insured I " +
              " where I.accountNo=C.accountNo " +
              " and DATEDIFF(day, I.startDate, C.claimDate)>=0 " +
              " and DATEDIFF(day, I.endDate, C.claimDate)<=0 " +
              " and C.insuredID=@insuredID ";
               
        cmd = new SqlCommand(sql, con);
        cmd.Parameters.AddWithValue("@insuredID", insuredID);
        
        dr = cmd.ExecuteReader();
        
        decimal total = 0;
        if (dr.Read())
        {
             total = dr.IsDBNull(0) ? 0 : dr.GetDecimal(0);
        }
        dr.Close();
        con.Close();
        return total;
    }


}